﻿using System;
using System.Data;
using System.Configuration;
using System.Data.SqlClient;
using Museum;

namespace Museum.DBAccess
{
    /// <summary>
    /// Summary description for DBAccess
    /// </summary>
    public class DBAccess
    {
        private static string strConnectionString = ConfigurationSettings.AppSettings["ConnectionString"].ToString();

        #region "Web Portal Methods"

        public static int IsUserValid(string username, string password, int role)
        {
            SqlParameter[] sqlParams = new SqlParameter[3];
            sqlParams[0] = new SqlParameter("@Username", username);
            sqlParams[1] = new SqlParameter("@Password", password);
            sqlParams[2] = new SqlParameter("@Role", role);

            object result = SqlHelper.ExecuteScalar(strConnectionString, CommandType.StoredProcedure, Constants.SP_VALIDATEUSER, sqlParams);

            if (result != null && Convert.ToInt32(result) > 0)
                return Convert.ToInt32(result);
            else
                return 0;
        }

        public static DataTable GetCountries()
        {
            DataSet ds = null;

            ds = SqlHelper.ExecuteDataset(strConnectionString, CommandType.StoredProcedure, Constants.SP_GETCOUNTRIES);

            if (ds != null && ds.Tables.Count > 0)
                return ds.Tables[0];
            else
                return null;
        }

        public static DataTable GetCities(int countryId, DateTime updatedDate)
        {
            DataSet ds = null;

            SqlParameter[] sqlParams = new SqlParameter[2];
            sqlParams[0] = new SqlParameter("@CountryId", countryId);
            sqlParams[1] = new SqlParameter("@UpdatedDate", updatedDate);

            ds = SqlHelper.ExecuteDataset(strConnectionString, CommandType.StoredProcedure, Constants.sp_GetCity, sqlParams);

            if (ds != null && ds.Tables.Count > 0)
                return ds.Tables[0];
            else
                return null;
        }

        public static DataTable GetMuseumById(int museumId)
        {
            DataSet ds = null;

            SqlParameter[] sqlParams = new SqlParameter[1];
            sqlParams[0] = new SqlParameter("@MuseumId", museumId);

            ds = SqlHelper.ExecuteDataset(strConnectionString, CommandType.StoredProcedure, Constants.SP_GETMUSEUMBYID, sqlParams);

            if (ds != null && ds.Tables.Count > 0)
                return ds.Tables[0];
            else
                return null;
        }

        public static DataTable GetMuseumBySchoolId(int schoolId)
        {
            DataSet ds = null;

            SqlParameter[] sqlParams = new SqlParameter[1];
            sqlParams[0] = new SqlParameter("@SchoolId", schoolId);

            ds = SqlHelper.ExecuteDataset(strConnectionString, CommandType.StoredProcedure, Constants.SP_GETMUSEUMBYSCHOOLID, sqlParams);

            if (ds != null && ds.Tables.Count > 0)
                return ds.Tables[0];
            else
                return null;
        }

        public static int DeleteMuseumData(int museumId)
        {
            int result = 0;

            SqlParameter[] sqlParams = new SqlParameter[1];
            sqlParams[0] = new SqlParameter("@MuseumId", museumId);

            result = SqlHelper.ExecuteNonQuery(strConnectionString, Constants.SP_DELETEMUSEUMDATA, sqlParams);

            return result;
        }

        public static int DeleteTourData(int artId)
        {
            int result = 0;

            SqlParameter[] sqlParams = new SqlParameter[1];
            sqlParams[0] = new SqlParameter("@ArtId", artId);

            result = SqlHelper.ExecuteNonQuery(strConnectionString, Constants.SP_DELETTOURDATA, sqlParams);

            return result;
        }

        public static int DeleteArtAssetData(int artAssetId)
        {
            int result = 0;

            SqlParameter[] sqlParams = new SqlParameter[1];
            sqlParams[0] = new SqlParameter("@ArtAssetId", artAssetId);

            result = SqlHelper.ExecuteNonQuery(strConnectionString, Constants.SP_DELETEARTASSETDATA, sqlParams);

            return result;
        }

        public static int DeleteSchoolData(int schoolId)
        {
            int result = 0;

            SqlParameter[] sqlParams = new SqlParameter[1];
            sqlParams[0] = new SqlParameter("@SchoolId", schoolId);

            result = SqlHelper.ExecuteNonQuery(strConnectionString, Constants.SP_DELETESCHOOLDATA, sqlParams);

            return result;
        }

        public static int DeleteReviewData(int reviewId)
        {
            int result = 0;

            SqlParameter[] sqlParams = new SqlParameter[1];
            sqlParams[0] = new SqlParameter("@ReviewId", reviewId);

            result = SqlHelper.ExecuteNonQuery(strConnectionString, Constants.SP_DELETEREVIEWDATA, sqlParams);

            return result;
        }

        public static int DeleteGameData(int gameId)
        {
            int result = 0;

            SqlParameter[] sqlParams = new SqlParameter[1];
            sqlParams[0] = new SqlParameter("@GameId", gameId);

            result = SqlHelper.ExecuteNonQuery(strConnectionString, Constants.SP_DELETEGAMEDATA, sqlParams);

            return result;
        }

        public static int DeleteCategoryData(int categoryId)
        {
            int result = 0;

            SqlParameter[] sqlParams = new SqlParameter[1];
            sqlParams[0] = new SqlParameter("@CategoryId", categoryId);

            result = SqlHelper.ExecuteNonQuery(strConnectionString, Constants.SP_DELETECATEGORYDATA, sqlParams);

            return result;
        }

        public static int DeleteVoucherData(int voucherId)
        {
            int result = 0;

            SqlParameter[] sqlParams = new SqlParameter[1];
            sqlParams[0] = new SqlParameter("@VoucherId", voucherId);

            result = SqlHelper.ExecuteNonQuery(strConnectionString, Constants.SP_DELETEVOUCHERDATA, sqlParams);

            return result;
        }

        public static int DeleteCityData(int cityId)
        {
            int result = 0;

            SqlParameter[] sqlParams = new SqlParameter[1];
            sqlParams[0] = new SqlParameter("@CityId", cityId);

            result = SqlHelper.ExecuteNonQuery(strConnectionString, Constants.SP_DELETECITYDATA, sqlParams);

            return result;
        }

        public static DataTable GetTourById(int artId)
        {
            DataSet ds = null;

            SqlParameter[] sqlParams = new SqlParameter[1];
            sqlParams[0] = new SqlParameter("@ArtId", artId);

            ds = SqlHelper.ExecuteDataset(strConnectionString, CommandType.StoredProcedure, Constants.SP_GETTOURBYID, sqlParams);

            if (ds != null && ds.Tables.Count > 0)
                return ds.Tables[0];
            else
                return null;
        }

        public static DataTable GetToursByMuseumId(int museumId)
        {
            DataSet ds = null;

            SqlParameter[] sqlParams = new SqlParameter[1];
            sqlParams[0] = new SqlParameter("@MuseumId", museumId);

            ds = SqlHelper.ExecuteDataset(strConnectionString, CommandType.StoredProcedure, Constants.SP_GETTOURSBYMUSEUMID, sqlParams);

            if (ds != null && ds.Tables.Count > 0)
                return ds.Tables[0];
            else
                return null;
        }

        public static DataTable GetArtAssetsByArtId(int artId)
        {
            DataSet ds = null;

            SqlParameter[] sqlParams = new SqlParameter[1];
            sqlParams[0] = new SqlParameter("@ArtId", artId);

            ds = SqlHelper.ExecuteDataset(strConnectionString, CommandType.StoredProcedure, Constants.SP_GETARTASSETSBYARTID, sqlParams);

            if (ds != null && ds.Tables.Count > 0)
                return ds.Tables[0];
            else
                return null;
        }

        public static int InsertOrUpdateCityDetails(int countryId, string cityName, int cityId)
        {
            SqlParameter[] sqlParams = new SqlParameter[3];
            sqlParams[0] = new SqlParameter("@CountryId", countryId);
            sqlParams[1] = new SqlParameter("@CityName", cityName);
            sqlParams[2] = new SqlParameter("@CityId", cityId);

            int result = SqlHelper.ExecuteNonQuery(strConnectionString, CommandType.StoredProcedure, Constants.SP_INSERTORUPDATECITYDETAILS, sqlParams);

            return result;
        }
        public static int UpdateSearchSettings(int MuseumId, bool IsArtName, bool IsFloor, bool Isstop, bool Isgallery, bool IsArtistName)
        {
            SqlParameter[] sqlParams = new SqlParameter[6];
            sqlParams[0] = new SqlParameter("@MuseumId", MuseumId);
            sqlParams[1] = new SqlParameter("@IsArtName", IsArtName);
            sqlParams[2] = new SqlParameter("@IsFloor", IsFloor);
            sqlParams[3] = new SqlParameter("@Isstop", Isstop);
            sqlParams[4] = new SqlParameter("@Isgallery", Isgallery);
            sqlParams[5] = new SqlParameter("@IsArtistName", IsArtistName);

            int result = SqlHelper.ExecuteNonQuery(strConnectionString, CommandType.StoredProcedure, Constants.sp_SearchSettings, sqlParams);

            return result;
        }

        public static int UpdateMuseumDetails(int museumId, string museumName, string museumAddress, string museumLink, string museumDetails)
        {
            SqlParameter[] sqlParams = new SqlParameter[5];
            sqlParams[0] = new SqlParameter("@MuseumId", museumId);
            sqlParams[1] = new SqlParameter("@MuseumName", museumName);
            sqlParams[2] = new SqlParameter("@MuseumAddress", museumAddress);
            sqlParams[3] = new SqlParameter("@MuseumLink", museumLink);
            sqlParams[4] = new SqlParameter("@MuseumDetails", museumDetails);

            int result = SqlHelper.ExecuteNonQuery(strConnectionString, CommandType.StoredProcedure, Constants.SP_UPDATEMUSEUMDETAILS, sqlParams);

            return result;
        }

        public static int InsertOrUpdateMuseumDetails(int museumId, string museumName, string museumAddress, string museumDetails,
            bool isPartner, int cityId, int museumRating, string museumLink)
        {
            SqlParameter[] sqlParams = new SqlParameter[8];
            sqlParams[0] = new SqlParameter("@MuseumId", museumId);
            sqlParams[1] = new SqlParameter("@MuseumName", museumName);
            sqlParams[2] = new SqlParameter("@MuseumAddress", museumAddress);
            sqlParams[3] = new SqlParameter("@MuseumDetails", museumDetails);
            sqlParams[4] = new SqlParameter("@CityId", cityId);
            sqlParams[5] = new SqlParameter("@IsPartner", isPartner);
            sqlParams[6] = new SqlParameter("@MuseumRating", museumRating);
            sqlParams[7] = new SqlParameter("@MuseumLink", museumLink);

            int result = SqlHelper.ExecuteNonQuery(strConnectionString, CommandType.StoredProcedure, Constants.SP_INSERTORUPDATEMUSEUMDETAILS, sqlParams);

            return result;
        }

        public static int InsertOrUpdateGameDetails(int gameId, int museumId, string gameName, string treasureLocation, string gameAlias, string gameDescription,
            DateTime expiryDate, bool isActive, int gameLevel, int gameRating, int gameDuration, int gameVersion, decimal gamePrice,
            string appLink, string gameThumbnailPath, string gameType, int gameDownloadCount, string treasureName,
            string treasureDescription, string treasureImagePath, int newGameID, int totalClues)
        {
            SqlParameter[] sqlParams = new SqlParameter[22];
            sqlParams[0] = new SqlParameter("@GameId", gameId);
            sqlParams[1] = new SqlParameter("@MuseumId", museumId);
            sqlParams[2] = new SqlParameter("@GameName", gameName);
            sqlParams[3] = new SqlParameter("@GameAlias", gameAlias);
            sqlParams[4] = new SqlParameter("@GameDescription", gameDescription);
            sqlParams[5] = new SqlParameter("@ExpiryDate", expiryDate);
            sqlParams[6] = new SqlParameter("@IsActive", isActive);
            sqlParams[7] = new SqlParameter("@GameLevel", gameLevel);
            sqlParams[8] = new SqlParameter("@GameRating", gameRating);
            sqlParams[9] = new SqlParameter("@GameDuration", gameDuration);
            sqlParams[10] = new SqlParameter("@GameVersion", gameVersion);
            sqlParams[11] = new SqlParameter("@GamePrice", gamePrice);
            sqlParams[12] = new SqlParameter("@AppLink", appLink);
            sqlParams[13] = new SqlParameter("@GameThumbnailPath", gameThumbnailPath);
            sqlParams[14] = new SqlParameter("@GameType", gameType);
            sqlParams[15] = new SqlParameter("@DownloadCount", gameDownloadCount);
            //treasure details
            sqlParams[16] = new SqlParameter("@TreasureName", treasureName);
            sqlParams[17] = new SqlParameter("@TreasureLocation", treasureLocation);
            sqlParams[18] = new SqlParameter("@TreasureDescription", treasureDescription);
            sqlParams[19] = new SqlParameter("@TreasureImagePath", treasureImagePath);
            sqlParams[20] = new SqlParameter("@TotalClues", totalClues);
            sqlParams[21] = new SqlParameter("@NewGameID", newGameID);
            sqlParams[21].Direction = ParameterDirection.Output;

            int result = SqlHelper.ExecuteNonQuery(strConnectionString, CommandType.StoredProcedure, Constants.SP_INSERTORUPDATEGAMEDETAILS, sqlParams);

            newGameID = Convert.ToInt32(sqlParams[21].Value);
            return newGameID;
        }

        public static int InsertOrUpdateClueDetails(int clueId, int gameId, string clueDetails, string clueDirections, int clueNumber, string findArt, string findWord,
            string answer, bool isCritical, int type, string filePath, string hintData)
        {
            SqlParameter[] sqlParams = new SqlParameter[12];
            sqlParams[0] = new SqlParameter("@ClueId", clueId);
            sqlParams[1] = new SqlParameter("@GameId", gameId);
            sqlParams[2] = new SqlParameter("@ClueDetails", clueDetails);
            sqlParams[3] = new SqlParameter("@DirectionDetails", clueDirections);
            sqlParams[4] = new SqlParameter("@ClueNumber", clueNumber);
            sqlParams[5] = new SqlParameter("@LocateArt", findArt);
            sqlParams[6] = new SqlParameter("@FindWordAt", findWord);
            sqlParams[7] = new SqlParameter("@Answer", answer);
            sqlParams[8] = new SqlParameter("@IsCritical", isCritical);
            sqlParams[9] = new SqlParameter("@Type", type);
            sqlParams[10] = new SqlParameter("@FilePath", filePath);
            sqlParams[11] = new SqlParameter("@HintData", hintData);

            int result = SqlHelper.ExecuteNonQuery(strConnectionString, CommandType.StoredProcedure, Constants.SP_INSERTORUPDATECLUEDETAILS, sqlParams);

            return result;
        }

        public static int InsertOrUpdateTourDetails(int museumId, int categoryId, int artId, string artName, string artistName, string artDescription,
            string stopNumber, string floorNumber, string gallery, string finalQuestion, string finalAnswer, string criticalWord,
            string imagePath1, string imagePath2, string imagePath3, string imagePath4, string imagePath5, string audioPath, string videoPath,
            string artImagePath, int artAssetID1, int artAssetID2, int artAssetID3, int artAssetID4, int artAssetID5, int artAssetID6, int artAssetID7)
        {
            SqlParameter[] sqlParams = new SqlParameter[27];

            sqlParams[0] = new SqlParameter("@MuseumId", museumId);
            sqlParams[1] = new SqlParameter("@CategoryId", categoryId);
            sqlParams[2] = new SqlParameter("@ArtId", artId);
            sqlParams[3] = new SqlParameter("@ArtName", artName);
            sqlParams[4] = new SqlParameter("@ArtistName", artistName);
            sqlParams[5] = new SqlParameter("@ArtDescription", artDescription);
            sqlParams[6] = new SqlParameter("@StopNumber", stopNumber);
            sqlParams[7] = new SqlParameter("@FloorNumber", floorNumber);
            sqlParams[8] = new SqlParameter("@Gallery", gallery);
            sqlParams[9] = new SqlParameter("@FinalQuestion", finalQuestion);
            sqlParams[10] = new SqlParameter("@FinalAnswer", finalAnswer);
            sqlParams[11] = new SqlParameter("@CriticalWord", criticalWord);
            sqlParams[12] = new SqlParameter("@ImagePath1", imagePath1);
            sqlParams[13] = new SqlParameter("@ImagePath2", imagePath2);
            sqlParams[14] = new SqlParameter("@ImagePath3", imagePath3);
            sqlParams[15] = new SqlParameter("@ImagePath4", imagePath4);
            sqlParams[16] = new SqlParameter("@ImagePath5", imagePath5);
            sqlParams[17] = new SqlParameter("@AudioPath", audioPath);
            sqlParams[18] = new SqlParameter("@VideoPath", videoPath);
            sqlParams[19] = new SqlParameter("@ArtImagePath", artImagePath);
            sqlParams[20] = new SqlParameter("@ArtAssetID1", artAssetID1);
            sqlParams[21] = new SqlParameter("@ArtAssetID2", artAssetID2);
            sqlParams[22] = new SqlParameter("@ArtAssetID3", artAssetID3);
            sqlParams[23] = new SqlParameter("@ArtAssetID4", artAssetID4);
            sqlParams[24] = new SqlParameter("@ArtAssetID5", artAssetID5);
            sqlParams[25] = new SqlParameter("@ArtAssetID6", artAssetID6);
            sqlParams[26] = new SqlParameter("@ArtAssetID7", artAssetID7);

            int result = SqlHelper.ExecuteNonQuery(strConnectionString, CommandType.StoredProcedure, Constants.SP_INSERTORUPDATETOURDETAILS, sqlParams);

            return result;
        }

        public static int InsertOrUpdateSchoolDetails(int schoolId, string museumIds, string schoolName, string schoolAddress, int cityId)
        {
            SqlParameter[] sqlParams = new SqlParameter[5];
            sqlParams[0] = new SqlParameter("@SchoolId", schoolId);
            sqlParams[1] = new SqlParameter("@MuseumIds", museumIds);
            sqlParams[2] = new SqlParameter("@SchoolName", schoolName);
            sqlParams[3] = new SqlParameter("@SchoolAddress", schoolAddress);
            sqlParams[4] = new SqlParameter("@CityId", cityId);

            int result = SqlHelper.ExecuteNonQuery(strConnectionString, CommandType.StoredProcedure, Constants.SP_INSERTORUPDATESCHOOLDETAILS, sqlParams);

            return result;
        }

        public static DataTable GetAllSchools()
        {
            DataSet ds = null;

            ds = SqlHelper.ExecuteDataset(strConnectionString, CommandType.StoredProcedure, Constants.SP_GETALLSCHOOLS);

            if (ds != null && ds.Tables.Count > 0)
                return ds.Tables[0];
            else
                return null;
        }

        public static DataTable GetAllMuseums()
        {
            DataSet ds = null;

            ds = SqlHelper.ExecuteDataset(strConnectionString, CommandType.StoredProcedure, Constants.SP_GETALLMUSEUMS);

            if (ds != null && ds.Tables.Count > 0)
                return ds.Tables[0];
            else
                return null;
        }

        public static DataTable GetAllGames()
        {
            DataSet ds = null;

            ds = SqlHelper.ExecuteDataset(strConnectionString, CommandType.StoredProcedure, Constants.SP_GETALLGAMES);

            if (ds != null && ds.Tables.Count > 0)
                return ds.Tables[0];
            else
                return null;
        }

        public static DataTable GetAllTours()
        {
            DataSet ds = null;

            ds = SqlHelper.ExecuteDataset(strConnectionString, CommandType.StoredProcedure, Constants.SP_GETALLTOURS);

            if (ds != null && ds.Tables.Count > 0)
                return ds.Tables[0];
            else
                return null;
        }

        public static DataTable GetAllCountries()
        {
            DataSet ds = null;

            ds = SqlHelper.ExecuteDataset(strConnectionString, CommandType.StoredProcedure, Constants.SP_GETALLCOUNTRIES);

            if (ds != null && ds.Tables.Count > 0)
                return ds.Tables[0];
            else
                return null;
        }

        public static DataTable GetAllCities()
        {
            DataSet ds = null;

            ds = SqlHelper.ExecuteDataset(strConnectionString, CommandType.StoredProcedure, Constants.SP_GETALLCITIES);

            if (ds != null && ds.Tables.Count > 0)
                return ds.Tables[0];
            else
                return null;
        }

        public static DataTable GetCategoryDetails(int categoryId)
        {
            DataSet ds = null;

            SqlParameter[] sqlParams = new SqlParameter[1];
            sqlParams[0] = new SqlParameter("@CategoryId", categoryId);

            ds = SqlHelper.ExecuteDataset(strConnectionString, CommandType.StoredProcedure, Constants.SP_GETCATEGORYDETAILS, sqlParams);

            if (ds != null && ds.Tables.Count > 0)
                return ds.Tables[0];
            else
                return null;
        }

        public static DataTable GetSchoolByMuseum(int museumId)
        {
            DataSet ds = null;

            SqlParameter[] sqlParams = new SqlParameter[1];
            sqlParams[0] = new SqlParameter("@MuseumId", museumId);

            ds = SqlHelper.ExecuteDataset(strConnectionString, CommandType.StoredProcedure, Constants.SP_GETSCHOOLBYMUSEUMID, sqlParams);

            if (ds != null && ds.Tables.Count > 0)
                return ds.Tables[0];
            else
                return null;
        }

        public static DataTable GetSchoolDetails(int schoolId)
        {
            DataSet ds = null;

            SqlParameter[] sqlParams = new SqlParameter[1];
            sqlParams[0] = new SqlParameter("@SchoolId", schoolId);

            ds = SqlHelper.ExecuteDataset(strConnectionString, CommandType.StoredProcedure, Constants.SP_GETSCHOOLDETAILS, sqlParams);

            if (ds != null && ds.Tables.Count > 0)
                return ds.Tables[0];
            else
                return null;
        }

        public static DataTable GetGamesBySchool(int schoolId)
        {
            DataSet ds = null;

            SqlParameter[] sqlParams = new SqlParameter[1];
            sqlParams[0] = new SqlParameter("@SchoolId", schoolId);

            ds = SqlHelper.ExecuteDataset(strConnectionString, CommandType.StoredProcedure, Constants.SP_GETGAMESBYSCHOOLID, sqlParams);

            if (ds != null && ds.Tables.Count > 0)
                return ds.Tables[0];
            else
                return null;
        }

        public static DataTable GetGameById(int gameId)
        {
            DataSet ds = null;

            SqlParameter[] sqlParams = new SqlParameter[1];
            sqlParams[0] = new SqlParameter("@GameId", gameId);

            ds = SqlHelper.ExecuteDataset(strConnectionString, CommandType.StoredProcedure, Constants.SP_GETGAMEBYID, sqlParams);

            if (ds != null && ds.Tables.Count > 0)
                return ds.Tables[0];
            else
                return null;
        }

        public static DataTable GetVoucherDetails(int gameId)
        {
            DataSet ds = null;

            SqlParameter[] sqlParams = new SqlParameter[1];
            sqlParams[0] = new SqlParameter("@GameId", gameId);

            ds = SqlHelper.ExecuteDataset(strConnectionString, CommandType.StoredProcedure, Constants.SP_GETVOUCHERDETAILS, sqlParams);

            if (ds != null && ds.Tables.Count > 0)
                return ds.Tables[0];
            else
                return null;
        }

        public static DataSet GetAnalyticsData(int gameId)
        {
            DataSet ds = null;

            SqlParameter[] sqlParams = new SqlParameter[1];
            sqlParams[0] = new SqlParameter("@GameId", gameId);

            ds = SqlHelper.ExecuteDataset(strConnectionString, CommandType.StoredProcedure, Constants.SP_GETANALYTICSDATA, sqlParams);

            ds.Tables[0].TableName = "Voucher";
            ds.Tables[1].TableName = "Game";

            if (ds != null && ds.Tables.Count > 0)
                return ds;
            else
                return null;
        }

        public static DataTable GetClueByGameId(int clueNumber, int gameId)
        {
            DataSet ds = null;

            SqlParameter[] sqlParams = new SqlParameter[2];
            sqlParams[0] = new SqlParameter("@ClueNumber", clueNumber);
            sqlParams[1] = new SqlParameter("@GameId", gameId);

            ds = SqlHelper.ExecuteDataset(strConnectionString, CommandType.StoredProcedure, Constants.SP_GETCLUEBYGAMEID, sqlParams);

            if (ds != null && ds.Tables.Count > 0)
                return ds.Tables[0];
            else
                return null;
        }

        #endregion

        public static DataTable GetMuseumByCity(string cityName)
        {
            DataSet ds = null;

            SqlParameter[] sqlParams = new SqlParameter[1];
            sqlParams[0] = new SqlParameter("@CityName", cityName);

            ds = SqlHelper.ExecuteDataset(strConnectionString, CommandType.StoredProcedure, Constants.SP_GETMUSEUMS, sqlParams);

            if (ds != null && ds.Tables.Count > 0)
                return ds.Tables[0];
            else
                return null;
        }

        public static string GetVoucherStatus(string voucher, int gameId)
        {
            SqlParameter[] sqlParams = new SqlParameter[2];
            sqlParams[0] = new SqlParameter("@Voucher", voucher);
            sqlParams[1] = new SqlParameter("@GameId", gameId);

            string VoucherStatus = SqlHelper.ExecuteScalar(strConnectionString, CommandType.StoredProcedure, Constants.SP_VALIDATEVOUCHER, sqlParams).ToString();

            return VoucherStatus;
        }

        public static DataTable GetGameByMuseum(int museumId)
        {
            DataSet ds = null;

            SqlParameter[] sqlParams = new SqlParameter[1];
            sqlParams[0] = new SqlParameter("@MuseumId", museumId);

            ds = SqlHelper.ExecuteDataset(strConnectionString, CommandType.StoredProcedure, Constants.SP_GETGAMESBYMUSEUMID, sqlParams);

            if (ds != null && ds.Tables.Count > 0)
                return ds.Tables[0];
            else
                return null;
        }

        public static DataTable GetGameByOption(int option, int museumId)
        {
            DataSet ds = null;

            SqlParameter[] sqlParams = new SqlParameter[2];
            sqlParams[0] = new SqlParameter("@Option", option);
            sqlParams[1] = new SqlParameter("@MuseumId", museumId);

            ds = SqlHelper.ExecuteDataset(strConnectionString, CommandType.StoredProcedure, Constants.SP_GETGAMESBYOPTION, sqlParams);

            if (ds != null && ds.Tables.Count > 0)
                return ds.Tables[0];
            else
                return null;
        }

        public static DataTable GetAllGamesByMuseum(int museumId)
        {
            DataSet ds = null;

            SqlParameter[] sqlParams = new SqlParameter[1];
            sqlParams[0] = new SqlParameter("@MuseumId", museumId);

            ds = SqlHelper.ExecuteDataset(strConnectionString, CommandType.StoredProcedure, Constants.SP_GETGAMES, sqlParams);

            if (ds != null && ds.Tables.Count > 0)
                return ds.Tables[0];
            else
                return null;
        }

        public static DataTable GetSearchSettingsForMuseum(int museumId)
        {
            DataSet ds = null;

            SqlParameter[] sqlParams = new SqlParameter[1];
            sqlParams[0] = new SqlParameter("@MuseumId", museumId);

            ds = SqlHelper.ExecuteDataset(strConnectionString, CommandType.StoredProcedure, Constants.SP_GETSEARCHSETTINGS, sqlParams);

            if (ds != null && ds.Tables.Count > 0)
                return ds.Tables[0];
            else
                return null;
        }
        public static DataSet GetGameByGameId(int gameId, int isFirstTimeDownload, int requestingDevice)
        {
            DataSet ds = null;

            SqlParameter[] sqlParams = new SqlParameter[3];
            sqlParams[0] = new SqlParameter("@GameId", gameId);
            sqlParams[1] = new SqlParameter("@IsFirstTimeDownload", isFirstTimeDownload);
            sqlParams[2] = new SqlParameter("@RequestingDevice", requestingDevice);

            ds = SqlHelper.ExecuteDataset(strConnectionString, CommandType.StoredProcedure, Constants.SP_DOWNLOADGAME, sqlParams);

            ds.Tables[0].TableName = "Game";
            ds.Tables[1].TableName = "Assets";
            ds.Tables[2].TableName = "Treasure";
            ds.Tables[3].TableName = "Hint";
            ds.Tables[4].TableName = "Clue";

            if (ds != null && ds.Tables.Count > 0)
                return ds;
            else
                return null;
        }

        public static bool GetGameUpdate(int gameId, int version)
        {
            SqlParameter[] sqlParams = new SqlParameter[2];
            sqlParams[0] = new SqlParameter("@GameId", gameId);
            sqlParams[1] = new SqlParameter("@Version", version);

            object result = SqlHelper.ExecuteScalar(strConnectionString, CommandType.StoredProcedure, Constants.SP_CHECKGAMEUPDATE, sqlParams);

            if (result != null && Convert.ToInt32(result) > 0)
                return true;
            else
                return false;
        }

        public static DataTable GetTopGameScore(int count, int gameId)
        {
            DataSet ds = null;

            SqlParameter[] sqlParams = new SqlParameter[2];
            sqlParams[0] = new SqlParameter("@Count", count);
            sqlParams[1] = new SqlParameter("@GameId", gameId);

            ds = SqlHelper.ExecuteDataset(strConnectionString, CommandType.StoredProcedure, Constants.SP_GETTOPGAMESCORE, sqlParams);

            if (ds != null && ds.Tables.Count > 0)
                return ds.Tables[0];
            else
                return null;
        }
        public static DataSet GetCategories(int museumId)
        {
            DataSet ds = null;

            SqlParameter[] sqlParams = new SqlParameter[1];
            sqlParams[0] = new SqlParameter("@MuseumId", museumId);

            ds = SqlHelper.ExecuteDataset(strConnectionString, CommandType.StoredProcedure, Constants.SP_GETCATEGORIES, sqlParams);

            if (ds.Tables.Count == 2)
            {
                ds.Tables[0].TableName = "Categories";
                ds.Tables[1].TableName = "SearchColumns";
            }

            if (ds != null && ds.Tables.Count > 0)
                return ds;
            else
                return null;
        }

        public static DataTable GetReviews(int gameId)
        {
            DataSet ds = null;

            SqlParameter[] sqlParams = new SqlParameter[1];
            sqlParams[0] = new SqlParameter("@GameId", gameId);

            ds = SqlHelper.ExecuteDataset(strConnectionString, CommandType.StoredProcedure, Constants.SP_GETREVIEWS, sqlParams);

            if (ds != null && ds.Tables.Count > 0)
                return ds.Tables[0];
            else
                return null;
        }


        public static DataSet GetTourData(int museumId, int fromIndex, int count)
        {
            DataSet ds = null;

            SqlParameter[] sqlParams = new SqlParameter[3];
            sqlParams[0] = new SqlParameter("@MuseumId", museumId);
            sqlParams[1] = new SqlParameter("@FromIndex", fromIndex);
            sqlParams[2] = new SqlParameter("@Count", count);

            ds = SqlHelper.ExecuteDataset(strConnectionString, CommandType.StoredProcedure, Constants.SP_GETTOUR, sqlParams);

            ds.Tables[0].TableName = "ArtMaster";
            ds.Tables[1].TableName = "ArtAssets";

            if (ds != null && ds.Tables.Count > 0)
                return ds;
            else
                return null;
        }
        public static DataTable GetSearchArt(string searchString, string searchInColumnName, int categoryId, int museumId, int fromIndex, int pageSize)
        {
            DataSet ds = null;

            SqlParameter[] sqlParams = new SqlParameter[6];
            sqlParams[0] = new SqlParameter("@SearchString", searchString);
            sqlParams[1] = new SqlParameter("@SearchInColumn", searchInColumnName);
            sqlParams[2] = new SqlParameter("@CategoryId", categoryId);
            sqlParams[3] = new SqlParameter("@MuseumId", museumId);
            sqlParams[4] = new SqlParameter("@FromIndex", fromIndex);
            sqlParams[5] = new SqlParameter("@PageSize", pageSize);

            ds = SqlHelper.ExecuteDataset(strConnectionString, CommandType.StoredProcedure, Constants.SP_GETSEARCHART, sqlParams);

            if (ds != null && ds.Tables.Count > 0)
                return ds.Tables[0];
            else
                return null;
        }

        public static DataTable GetArts(int categoryId, int fromIndex, int pageSize)
        {
            DataSet ds = null;

            SqlParameter[] sqlParams = new SqlParameter[3];
            sqlParams[0] = new SqlParameter("@CategoryId", categoryId);
            sqlParams[1] = new SqlParameter("@FromIndex", fromIndex);
            sqlParams[2] = new SqlParameter("@PageSize", pageSize);

            ds = SqlHelper.ExecuteDataset(strConnectionString, CommandType.StoredProcedure, Constants.SP_GETARTS, sqlParams);

            if (ds != null && ds.Tables.Count > 0)
                return ds.Tables[0];
            else
                return null;
        }

        public static DataTable GetCity(int countryId, DateTime updatedDate)
        {
            DataSet ds = null;

            SqlParameter[] sqlParams = new SqlParameter[2];
            sqlParams[0] = new SqlParameter("@CountryId", countryId);
            sqlParams[1] = new SqlParameter("@UpdatedDate", updatedDate);

            ds = SqlHelper.ExecuteDataset(strConnectionString, CommandType.StoredProcedure, Constants.sp_GetCity, sqlParams);

            if (ds != null && ds.Tables.Count > 0)
                return ds.Tables[0];
            else
                return null;
        }


        public static DataSet GetArtDetails(int artId)
        {
            DataSet ds = null;

            SqlParameter[] sqlParams = new SqlParameter[1];
            sqlParams[0] = new SqlParameter("@ArtId", artId);

            ds = SqlHelper.ExecuteDataset(strConnectionString, CommandType.StoredProcedure, Constants.SP_GETARTDETAIL, sqlParams);

            ds.Tables[0].TableName = "ArtMaster";
            ds.Tables[1].TableName = "ArtAssets";

            if (ds != null && ds.Tables.Count > 0)
                return ds;
            else
                return null;
        }


        public static DataTable GetTreasureDetails(int treasureId)
        {
            DataSet ds = null;

            SqlParameter[] sqlParams = new SqlParameter[1];
            sqlParams[0] = new SqlParameter("@TreasureId", treasureId);

            ds = SqlHelper.ExecuteDataset(strConnectionString, CommandType.StoredProcedure, Constants.SP_GETTREASUREDETAILS, sqlParams);

            if (ds != null && ds.Tables.Count > 0)
                return ds.Tables[0];
            else
                return null;
        }

        public static int InsertGameScore(int gameId, string userName, int score)
        {
            SqlParameter[] sqlParams = new SqlParameter[3];
            sqlParams[0] = new SqlParameter("@GameId", gameId);
            sqlParams[1] = new SqlParameter("@UserName", userName);
            sqlParams[2] = new SqlParameter("@Score", score);

            return SqlHelper.ExecuteNonQuery(strConnectionString, CommandType.StoredProcedure, Constants.SP_INSERTGAMESCORE, sqlParams);
        }
        public static int InsertGameReview(int gameId, string review, string userName, short rating)
        {
            SqlParameter[] sqlParams = new SqlParameter[4];
            sqlParams[0] = new SqlParameter("@GameId", gameId);
            sqlParams[1] = new SqlParameter("@Review", review);
            sqlParams[2] = new SqlParameter("@UserName", userName);
            sqlParams[3] = new SqlParameter("@Rating", rating);

            return SqlHelper.ExecuteNonQuery(strConnectionString, CommandType.StoredProcedure, Constants.SP_INSERTREVIEW, sqlParams);
        }
        public static int InsertVoucher(int gameId, string voucherDetails, DateTime expiryDate, string clientName, decimal voucherFixedPrice, decimal perVoucherPrice)
        {
            SqlParameter[] sqlParams = new SqlParameter[6];
            sqlParams[0] = new SqlParameter("@GameId", gameId);
            sqlParams[1] = new SqlParameter("@VoucherDetails", voucherDetails);
            sqlParams[2] = new SqlParameter("@ExpiryDate", expiryDate);
            sqlParams[3] = new SqlParameter("@ClientName", clientName);
            sqlParams[4] = new SqlParameter("@VoucherFixedPrice", voucherFixedPrice);
            sqlParams[5] = new SqlParameter("@PerVoucherPrice", perVoucherPrice);

            return SqlHelper.ExecuteNonQuery(strConnectionString, CommandType.StoredProcedure, Constants.SP_INSERTVOUCHER, sqlParams);
        }

        public static int InsertOrUpdateCategory(int museumId, string categoryName, int categoryId)
        {
            SqlParameter[] sqlParams = new SqlParameter[3];
            sqlParams[0] = new SqlParameter("@MuseumId", museumId);
            sqlParams[1] = new SqlParameter("@CategoryName", categoryName);
            sqlParams[2] = new SqlParameter("@CategoryId", categoryId);

            return SqlHelper.ExecuteNonQuery(strConnectionString, CommandType.StoredProcedure, Constants.SP_INSERTORUPDATECATEGORY, sqlParams);
        }

        public static int InsertOrUpdateUser(int museumId, int role, string username, string oldPassword, string newPassword)
        {
            SqlParameter[] sqlParams = new SqlParameter[5];
            sqlParams[0] = new SqlParameter("@MuseumId", museumId);
            sqlParams[1] = new SqlParameter("@Role", role);
            sqlParams[2] = new SqlParameter("@UserName", username);
            sqlParams[3] = new SqlParameter("@OldPassword", oldPassword);
            sqlParams[4] = new SqlParameter("@NewPassword", newPassword);

            return SqlHelper.ExecuteNonQuery(strConnectionString, CommandType.StoredProcedure, Constants.SP_INSERTORUPDATEUSER, sqlParams);
        }

        public static DataTable GetUserDetails(int museumId)
        {
            DataSet ds = null;

            SqlParameter[] sqlParams = new SqlParameter[1];
            sqlParams[0] = new SqlParameter("@MuseumId", museumId);

            ds = SqlHelper.ExecuteDataset(strConnectionString, CommandType.StoredProcedure, Constants.SP_GETUSERDETAILS, sqlParams);

            if (ds != null && ds.Tables.Count > 0)
                return ds.Tables[0];
            else
                return null;
        }
    }
}
